package fy.erp.dao;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.json.JSONArray;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.util.DBUtil;
import fy.util.MyUtil;


public class QuerySubjectInfoDao {
	private String field;
	private String operate;
	private String reset;

	private String result;

	private static String[] querySubjectByCodeSQLArray;
	private static String[] querySubjectByNameSQLArray;

	public String getResult() {
		return result;
	}

	public void setField(String field) {
		this.field = field;
	}

	public void setOperate(String operate) {
		this.operate = operate;
	}

	public void setReset(String reset) {
		this.reset = reset;
	}

	private void readSetting() {
		if (querySubjectByCodeSQLArray == null || "1".equals(reset)) {
			SAXReader sr = new SAXReader();
			try {
				Document doc = sr.read(new File(MyUtil
						.getRealPath(QuerySubjectInfoDao.class)
						+ "subjectSetting.xml"));
				Element root = doc.getRootElement();
				List<Element> list = (List<Element>) root.elements();
				querySubjectByCodeSQLArray = new String[list.size()];
				querySubjectByNameSQLArray = new String[list.size()];

				for (int i = 0, length = list.size(); i < length; i++) {
					String table = list.get(i).attributeValue("name");
					String codeField = null;
					String nameField = null;
					String costField = null;
					String tenThousand = null;
					List<Element> children = (List<Element>) list.get(i)
							.elements();
					for (int j = 0, size = children.size(); j < size; j++) {
						Element child = children.get(j);
						String name = child.attributeValue("name");
						String value = child.attributeValue("value");
						if ("code".equals(name)) {
							codeField = value;
						} else if ("name".equals(name)) {
							nameField = value;
						} else if ("cost".equals(name)) {
							costField = value;
							tenThousand = child.attributeValue("tenThousand");
						}
						child = null;
					}
					querySubjectByCodeSQLArray[i] = new StringBuilder()
							.append("select f.")
							.append(codeField)
							.append(", f.")
							.append(nameField)
							.append(", to_number(f.")
							.append(costField)
							.append(") * ")
							.append("true".equals(tenThousand) ? 10000 : 1)
							.append(", to_char(f.start_date, 'YYYY-MM-DD hh24:mi') from ")
							.append(table)
							.append(" f where f.")
							.append(codeField)
							.append(" like '%'||?||'%' and f.start_date is not null and f.finishedflag=1 order by f.start_date desc")
							.toString();
					querySubjectByNameSQLArray[i] = new StringBuilder()
							.append("select f.")
							.append(codeField)
							.append(", f.")
							.append(nameField)
							.append(", to_number(f.")
							.append(costField)
							.append(") * ")
							.append("true".equals(tenThousand) ? 10000 : 1)
							.append(", to_char(f.start_date, 'YYYY-MM-DD hh24:mi') from ")
							.append(table)
							.append(" f where f.")
							.append(nameField)
							.append(" like '%'||?||'%' and f.start_date is not null and f.finishedflag=1 order by f.start_date desc")
							.toString();
					children = null;
				}
				root = null;
				list = null;
				doc = null;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public boolean query() {
		boolean success = false;
		readSetting();
		String sql = null;
		if ("1".equals(operate)) {
			sql = "select v.项目编号,v.项目名称,v.预计费用,v.申请日期 from zzc_all_project_view1 v where v.项目编号 like '%'||?||'%'";
		} else if ("2".equals(operate)) {
			sql = "select v.项目编号,v.项目名称,v.预计费用,v.申请日期 from zzc_all_project_view1 v where v.项目名称 like '%'||?||'%'";
		}
		if (sql != null) {
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;

			try {
				conn = DBUtil.connect(FyConfig.getOA_DB_CONFIG());

				JSONArray ja = new JSONArray();
				pst = conn.prepareStatement(sql);
				pst.setString(1, field);
				rs = pst.executeQuery();
				while (rs.next()) {
					JSONObject jo = new JSONObject();
					jo.put("code", rs.getString(1));
					jo.put("name", rs.getString(2));
//					jo.put("cost", rs.getString(3));
					jo.put("time", rs.getString(4));
					ja.put(jo);
				}
				DBUtil.close(rs, pst);
				result = ja.toString();
				success = true;
			} catch (Exception e) {
				e.printStackTrace();
				result = "'数据库异常，请重试，或者联系OA系统管理员'";
			} finally {
				DBUtil.close(rs, pst, conn);
			}
		} else {
			result = "'无法生存SQL查询语句，请重试，或者联系OA系统管理员'";
		}
		return success;
	}
}
